const Sequelize = require("sequelize");
const mysql2 = require("mysql2");

module.exports = class Db {
  static #sequelize;
  static #database;
  static #user;
  static #password;
  static #host;
  static SQL_TYPE_SELECT = Sequelize.QueryTypes.SELECT;
  static SQL_TYPE_INSERT = Sequelize.QueryTypes.INSERT;
  static SQL_TYPE_UPDATE = Sequelize.QueryTypes.UPDATE;
  static SQL_TYPE_DELETE = Sequelize.QueryTypes.DELETE;
  static Op = Sequelize.Op;
  static #logger;

  static init(host, database, user, password, logger = false) {
    this.#host = host;
    this.#database = database;
    this.#user = user;
    this.#password = password;
    this.#logger = logger;
  }

  static getInstance() {
    if (!this.#sequelize) {
      if (this.#host) {
        this.#sequelize = new Sequelize(
          this.#database,
          this.#user,
          this.#password,
          {
            host: this.#host,
            dialect: "mysql",
            dialectModule: mysql2,
            timezone: "+08:00",
            define: {
              timestamps: false,
            },
            pool: {
              max: 30,
              min: 0,
              idle: 10000,
            },
            logging: this.#logger ? console.log : false,
            dialectOptions: {
              compress: true,
            },
          }
        );
      } else {
        throw new Error("DB not init");
      }
    }
    return this.#sequelize;
  }

  static async add(model, params = {}) {
    try {
      return await model(this.getInstance()).create(params);
    } catch (e) {
      const message = e.original ? e.original.sqlMessage : "";
      if (message.indexOf("Duplicate") >= 0) {
        throw new Error("不可重复内容：" + message.split("'")[1]);
      } else {
        throw e;
      }
    }
  }

  static async update(model, params = {}, where = {}) {
    try {
      return await model(this.getInstance()).update(params, {
        where: where,
      });
    } catch (e) {
      const message = e.original ? e.original.sqlMessage : "";
      if (message && message.includes("Duplicate")) {
        throw new Error("不可重复内容：" + message.split("'")[1]);
      } else {
        throw e;
      }
    }
  }

  /**
   * 比对增删改
   * @param {*} model 模型
   * @param {*} newData 新数据
   * @param {*} oldData 旧数据
   * @param {*} keyColumn 比对列
   * @param {*} needDelete 是否需要删除多余的
   * @param {*} compareColumn 比较列有没有变化
   */
  static async fullUpdate(
    model,
    newData = [],
    oldData = [],
    keyColumn,
    needDelete = false,
    compareColumn = []
  ) {
    let updateData = [];
    let addData = [];
    for (let nNewData of newData) {
      const nOldIndex = oldData.findIndex(
        (nOld) => nOld[keyColumn] == nNewData[keyColumn]
      );
      if (nOldIndex == -1) {
        addData.push(await this.add(model, nNewData));
      } else {
        const nOldData = oldData[nOldIndex];
        let needUpdate = false;
        if (compareColumn.length > 0) {
          for (let nColumn of compareColumn) {
            if (nOldData[nColumn] != nNewData[nColumn]) {
              console.log("Diff", nOldData[nColumn], nNewData[nColumn]);
              needUpdate = true;
              break;
            }
          }
        } else {
          needUpdate = true;
        }
        if (needUpdate) {
          let where = {};
          // 有主键的话，使用主键
          if (nOldData.id) {
            where.id = nOldData.id;
          } else {
            where[keyColumn] = nNewData[keyColumn];
          }
          // 只更新指定的字段
          let setValues = {};
          for (let nColumn of compareColumn) {
            setValues[nColumn] = nNewData[nColumn];
          }
          updateData.push(await this.update(model, setValues, where));
        }
        // 剔除已经处理的
        oldData.splice(nOldIndex, 1);
      }
    }
    // 删除旧剩余的
    if (needDelete) {
      for (let n of oldData) {
        await n.destroy();
      }
    }
    return { addData: addData, updateData: updateData };
  }

  /**
   * 查询多个数据
   * @param {*} model 模型
   * @param {*} params 查询参数，支持符号标记，如Db.Op.lt，具体参考https://sequelize.org/master/manual/model-querying-basics.html#operators
   * @param {*} order 排序，参考默认值
   * @param {*} page 页数
   * @param {*} size 每页数量
   * @param {*} attributes 只返回属性
   * @returns
   */
  static async find(
    model,
    params = {},
    order = [["id", "desc"]],
    page = 1,
    size = 50,
    attributes = { exclude: [""] }
  ) {
    return await model(this.getInstance()).findAndCountAll({
      where: params,
      offset: size * (page - 1),
      limit: size,
      order: order,
      attributes: attributes,
    });
  }

  /**
   * 查询所有数据，不分页
   * @param {*} model 模型
   * @param {*} params 查询参数，支持符号标记，如Db.Op.lt，具体参考https://sequelize.org/master/manual/model-querying-basics.html#operators
   * @param {*} order 排序，参考默认值
   * @param {*} attributes 只返回属性
   * @returns
   */
  static async findAll(
    model,
    params = {},
    order = [["id", "desc"]],
    attributes = { exclude: [""] }
  ) {
    return await model(this.getInstance()).findAll({
      where: params,
      order: order,
      attributes: attributes,
    });
  }

  static async findOne(
    model,
    params = {},
    order = [["id", "desc"]],
    attributes = { exclude: [""] }
  ) {
    return await model(this.getInstance()).findOne({
      where: params,
      order: order,
      attributes: attributes,
    });
  }

  static async delOne(model, params = {}, order = [["id", "desc"]]) {
    let data = await model(this.getInstance()).findOne({
      where: params,
      order: order,
    });
    if (data == null) {
      throw new Error("数据不存在");
    } else {
      await data.destroy();
    }
  }

  static async sql(
    sql,
    values = [],
    type = this.SQL_TYPE_SELECT,
    modelFormat = false
  ) {
    const result = await this.getInstance().query(sql, {
      type: type,
      replacements: values,
    });
    if (modelFormat) {
      // 如果不为空，循环转换为驼峰
      if (result) {
        for (let n of result) {
          for (let key in n) {
            if (key.includes("_")) {
              n[this._toHump(key)] = n[key];
              delete n[key];
            }
          }
        }
      }
    }
    return result;
  }

  static _toHump(text) {
    return text.replace(/\_(\w)/g, (all, letter) => {
      return letter.toUpperCase();
    });
  }
};
